package com.sailfish.springbootdemo.dao.db7;

import com.sailfish.springbootdemo.common.DB7TableName;
import com.sailfish.springbootdemo.pojo.db7.TbKnowledgeBaseData;
import com.sailfish.springbootdemo.pojo.db7.TbKnowledgeBaseDataDTO;
import com.sailfish.springbootdemo.pojo.db7.TbTaskVersion;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.cursor.Cursor;

import java.util.*;

public interface TbKnowledgeBaseDataDao {

    @Insert("<script>" +
            "insert into " + DB7TableName.TB_KNOWLEDGE_BASE_DATA + " " +
            "( "+
            "flow_no, " +
            "flow_level, " +
            "version, " +
            "template_type, " +
            "parent_id, " +
            "param_name, " +
            "param_name_en, " +
            "param_key, " +
            "parent_param_key, " +
            "param_type, " +
            "param_value, " +
            "level, " +
            "select_level, " +
            "parent_select_id, " +
            "tip, " +
            "remark, " +
            "create_user, " +
            "create_time, " +
            "update_user, " +
            "update_time, " +
            "sort, " +
            "row_sort, " +
            "is_value, " +
            "knowledge_base_key, " +
            "is_default_push" +
            ")"+
            "values "+
            "<foreach collection=\"entities\" item=\"entity\" separator=\",\">" +
            "(" +
            "#{entity.flow_no}, " +
            "#{entity.flow_level}, " +
            "#{entity.version}, " +
            "#{entity.template_type}, " +
            "#{entity.parent_id}, " +
            "#{entity.param_name}, " +
            "#{entity.param_name_en}, " +
            "#{entity.param_key}, " +
            "#{entity.parent_param_key}, " +
            "#{entity.param_type}, " +
            "#{entity.param_value}, " +
            "#{entity.level}, " +
            "#{entity.select_level}, " +
            "#{entity.parent_select_id}, " +
            "#{entity.tip}, " +
            "#{entity.remark}, " +
            "#{entity.create_user}, " +
            "#{entity.create_time}, " +
            "#{entity.update_user}, " +
            "#{entity.update_time}, " +
            "#{entity.sort}, " +
            "#{entity.row_sort}, " +
            "#{entity.is_value}, " +
            "#{entity.knowledge_base_key}, " +
            "#{entity.is_default_push}" +
            ")"+
            "</foreach>"+
            "</script>"
    )
    Integer insertTbKnowledgeBaseDataBatch(@Param("entities") List<TbKnowledgeBaseData> entities);

    @Delete("<script>" +
            "delete from " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " where knowledge_base_key = #{entity.knowledge_base_key} " +
            "</script>")
    Integer deleteTaskTemplateData(@Param("entity") TbKnowledgeBaseData entity);

    @Select("<script> " +
            "SELECT id,\n" +
            "flow_no,\n" +
            "flow_level,\n" +
            "version,\n" +
            "template_type,\n" +
            "parent_id,\n" +
            "param_name,\n" +
            "param_name_en,\n" +
            "param_key,\n" +
            "parent_param_key,\n" +
            "param_type,\n" +
            "param_value,\n" +
            "level,\n" +
            "select_level,\n" +
            "parent_select_id,\n" +
            "tip,\n" +
            "remark,\n" +
            "create_user,\n" +
            "create_time,\n" +
            "update_user,\n" +
            "update_time,\n" +
            "sort,\n" +
            "row_sort,\n" +
            "is_value,\n" +
            "knowledge_base_key,\n" +
            "IFNULL(is_default_push,0) AS is_default_push " +
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " WHERE 1=1 " +
            "<if test='baseKey != null and baseKey != \"\"'> " +
            " AND knowledge_base_key = #{baseKey} " +
            "</if> " +
            "</script>")
    List<TbKnowledgeBaseData> getByBaseKey(@Param("baseKey")String baseKey);


    @Update("<script>" +
            "UPDATE " + DB7TableName.TB_KNOWLEDGE_BASE_DATA + " " +
            "SET " +
            "flow_no = #{entity.flow_no}, " +
            "flow_level = #{entity.flow_level}, " +
            "version = #{entity.version}, " +
            "template_type = #{entity.template_type}, " +
            "parent_id = #{entity.parent_id}, " +
            "param_name = #{entity.param_name}, " +
            "param_name_en = #{entity.param_name_en}, " +
            "param_key = #{entity.param_key}, " +
            "parent_param_key = #{entity.parent_param_key}, " +
            "param_type = #{entity.param_type}, " +
            "param_value = #{entity.param_value}, " +
            "level = #{entity.level}, " +
            "select_level = #{entity.select_level}, " +
            "parent_select_id = #{entity.parent_select_id}, " +
            "tip = #{entity.tip}, " +
            "remark = #{entity.remark}, " +
            "create_user = #{entity.create_user}, " +
            "create_time = #{entity.create_time}, " +
            "update_user = #{entity.update_user}, " +
            "update_time = #{entity.update_time}, " +
            "sort = #{entity.sort}, " +
            "row_sort = #{entity.row_sort}, " +
            "is_value = #{entity.is_value}, " +
            "knowledge_base_key = #{entity.knowledge_base_key}, " +
            "is_default_push = #{entity.is_default_push} " +
            "<foreach collection=\"entities\" item=\"entity\" separator=\";\"> " +
            "WHERE id = #{entity.id} " +
            "</foreach>" +
            "</script>")
    Integer batchUpdate(@Param("entities") List<TbKnowledgeBaseData> entities);
    @Update("UPDATE " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " SET is_default_push = #{isDefaultPush}, " +
            " update_user = #{updateUser}, " +
            " update_time = #{updateTime} " +
            " WHERE knowledge_base_key = #{knowledgeBaseKey}")
    int updateIsDefaultPush(@Param("knowledgeBaseKey") String knowledgeBaseKey,
                            @Param("isDefaultPush") Integer isDefaultPush,
                            @Param("updateUser") String updateUser,
                            @Param("updateTime") Date updateTime);
    @Select("<script> " +
            "SELECT * " +
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " WHERE 1=1 " +
            "<if test='baseKeyList != null and !baseKeyList.isEmpty()'> " +
            " AND knowledge_base_key IN " +
            "<foreach item='item' index='index' collection='baseKeyList' open='(' separator=',' close=')'> " +
            "#{item} " +
            "</foreach>" +
            "</if>" +
            "<if test='baseKeyList == null or baseKeyList.isEmpty()'> " +
            " AND 1=0 " + // baseKeyList为空,直接返回空list
            "</if>" +
            " order by flow_no desc" +
            "</script>")
    List<TbKnowledgeBaseData> getByBaseKeyList(@Param("baseKeyList") Set<String> baseKeyList);

    @Select("<script> " +
            "SELECT param_key," +
            " param_value," +
            " parent_param_key," +
            " version, " +
            " template_type, " +
            " flow_no," +
            " row_sort," +
            " param_type," +
            " is_value," +
            " is_default_push FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA + " " +
            "WHERE 1=1 " +
            "<if test='baseKeyList != null and !baseKeyList.isEmpty()'> " +
            " AND knowledge_base_key IN " +
            "<foreach item='item' index='index' collection='baseKeyList' open='(' separator=',' close=')'> " +
            "#{item} " +
            "</foreach> " +
            "</if> " +
            " ORDER BY flow_no DESC " +
            " LIMIT #{offset}, #{limit} " +
            "</script>")
    List<TbKnowledgeBaseData> getByBaseKeyListPaged(
            @Param("baseKeyList") Set<String> baseKeyList,
            @Param("offset") int offset,
            @Param("limit") int limit
    );

    @Select("<script> " +
            "SELECT knowledge_base_key, param_key, param_value, parent_param_key, version, template_type, " +
            "       flow_no, row_sort, param_type, is_value, is_default_push " +
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA + " " +
            "WHERE 1=1" +
            "<if test='baseKeyList != null and !baseKeyList.isEmpty()'> " +
            " AND knowledge_base_key IN " +
            "<foreach item='item' index='index' collection='baseKeyList' open='(' separator=',' close=')'>#{item}</foreach> " +
            "</if> " +
            "<if test='baseKeyList == null or baseKeyList.isEmpty()'> " +
            " AND 1=0" + // baseKeyList为空,直接返回空list
            "</if>" +
            " AND row_sort != -1" +
            " ORDER BY flow_no DESC " +
            "</script>")
    Cursor<TbKnowledgeBaseData> scanAllByBaseKeys(@Param("baseKeyList") List<String> baseKeyList);

    @Select("<script> " +
            "SELECT version " +
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " WHERE 1=1 " +
            "<if test='curFlowNo != null and curFlowNo != \"\"'> " +
            " AND flow_no = #{curFlowNo} " +
            " order by version asc limit 1" +
            "</if> " +
            "</script>")
    String getVersionByFlowNo(@Param("curFlowNo")String curFlowNo);

    @Select("<script> " +
            "SELECT flow_no, version " +
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " WHERE 1=1 " +
            "<if test='flowList != null and !flowList.isEmpty()'> " +
            " AND flow_no IN " +
            "<foreach collection='flowList' item='flowNo' open='(' separator=',' close=')'> " +
            "#{flowNo} " +
            "</foreach> " +
            "</if>" +
            " GROUP BY flow_no, version" +
            "</script>")
    List<Map<String, Object>> getVersionByFlowNoList(@Param("flowList") List<String> flowList);

    @Select("<script> " +
            "SELECT is_default_push " +
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " WHERE 1=1 " +
            "<if test='baseKey != null and baseKey != \"\"'> " +
            " AND knowledge_base_key = #{baseKey} " +
            " order by is_default_push asc limit 1" +
            "</if> " +
            "</script>")
    Integer getIsPushByBaseKey(@Param("baseKey")String baseKey);

    @Select("<script> " +
        "SELECT DISTINCT knowledge_base_key, flow_no " +
        "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
        " WHERE 1=1 " +
        "<if test='templateType != null and templateType != \"\"'> " +
        "AND template_type = #{templateType} " +
        "</if> " +
        "ORDER BY flow_no DESC" +
         " LIMIT #{offset}, #{limit} " +
        "</script>")
    List<TbKnowledgeBaseDataDTO> getBaseKeyByTypePaged(@Param("templateType") String templateType,
                                                       @Param("offset") int offset,
                                                       @Param("limit") int limit);

    @Select("<script> " +
            "SELECT row_sort " +
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " WHERE 1=1" +
            "<if test='baseKey != null and baseKey != \"\"'> " +
            " AND knowledge_base_key = #{baseKey} " +
            "</if> " +
            " order by row_sort desc limit 1" +
            "</script>")
    Integer getMaxRowSortByLimit(
            @Param("baseKey") String baseKey);

    @Select("<script>" +
            "SELECT DISTINCT knowledge_base_key, flow_no FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA  +
            " WHERE 1=1 " +
            "<if test='key0 != null and key1 != null'> " +
            "  AND (param_key = #{key0} " +
            "  OR param_key = #{key1}) " +
            "</if> " +
            "  AND param_value LIKE '%*%' " +
            "  ORDER BY flow_no DESC" +
            "</script>")
    List<TbKnowledgeBaseDataDTO> getValueContainWildcard(
            @Param("key0") String key0,
            @Param("key1") String key1
    );
    /**
     * 创建临时表，用于存储查询条件
     *
     * @param tempTableName 临时表名称
     */
    @Insert("CREATE TEMPORARY TABLE ${tempTableName} (" +
            "idx INT, " +
            "param_key VARCHAR(255), " +
            "param_value VARCHAR(255), " +
            "contains_star BOOLEAN" +
            ")")
    void createTempTable(@Param("tempTableName") String tempTableName);

    /**
     * 批量插入条件到临时表
     *
     * @param tempTableName 临时表名称
     * @param conditions    条件列表，每个条件包含 idx、param_key、param_value 和 contains_star
     */
    @Insert("<script>" +
            "INSERT INTO ${tempTableName} (idx, param_key, param_value, contains_star) VALUES " +
            "<foreach collection='conditions' item='condition' separator=','>" +
            "(#{condition.idx}, #{condition.param_key}, #{condition.param_value}, #{condition.contains_star})" +
            "</foreach>" +
            "</script>")
    void insertIntoTempTable(
            @Param("tempTableName") String tempTableName,
            @Param("conditions") List<Map<String, Object>> conditions
    );

    /**
     * 删除临时表
     *
     * @param tempTableName 临时表名称
     */
    @Insert("DROP TEMPORARY TABLE IF EXISTS ${tempTableName}")
    void dropTempTable(@Param("tempTableName") String tempTableName);

    /**
     * 查询符合条件的 knowledge_base_key、flow_no 和 matched_indexes
     *
     * @param tempTableName 临时表名称
     * @param conditionCount 条件数量
     * @return 包含 knowledge_base_key、flow_no 和 matched_indexes 的结果
     */
    @Select("SELECT d.knowledge_base_key, d.flow_no, " +
            "GROUP_CONCAT(DISTINCT t.idx ORDER BY t.idx SEPARATOR ',') AS matched_indexes " +
            "FROM tb_knowledge_base_data d " +
            "INNER JOIN ${tempTableName} t ON d.param_key = t.param_key " +
            "AND (" +
            "    (t.contains_star = TRUE AND d.param_value LIKE t.param_value) " +
            "    OR (t.contains_star = FALSE AND d.param_value = t.param_value) " +
            ") " +
            "GROUP BY d.knowledge_base_key, d.flow_no " +
            "HAVING COUNT(DISTINCT t.idx) = #{conditionCount} " +
            "ORDER BY d.flow_no DESC")
    List<Map<String, Object>> getValidByTempTable(
            @Param("tempTableName") String tempTableName,
            @Param("conditionCount") int conditionCount
    );

    @Select("<script> " +
            "SELECT * " +
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " WHERE 1=1 and IFNULL(param_value,'')!='' " +
            "<if test='param_name != null and param_name != \"\"'> " +
            " AND param_name = #{param_name} " +
            "</if> " +
            "</script>")
    List<TbKnowledgeBaseData> getAllByParamName(@Param("param_name")String paramName);

    @Select("<script> " +
            "select \n" +
            "*" +
            " from " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " where 1=1" +
            " and template_type = #{type}" +
            " order by flow_no desc" +
            "</script>")
    List<TbKnowledgeBaseData> getAllKnowledgeByType(@Param("type") String type);
    @Update("UPDATE " + DB7TableName.TB_KNOWLEDGE_BASE_DATA +
            " SET param_value = #{param_value} " +
            " WHERE knowledge_base_key = #{knowledgeBaseKey}  AND param_name = #{param_name} AND row_sort>=0"
    )
    Integer updatePkgName(@Param("knowledgeBaseKey") String knowledgeBaseKey,
                              @Param("param_name")String paramName,
                            @Param("param_value") String param_value);

    @Select({"<script>",
            "SELECT ",
            "knowledge_base_key, flow_no, param_value, template_type, version ",
            "FROM " + DB7TableName.TB_KNOWLEDGE_BASE_DATA,
            "WHERE 1=1",
            "<if test='type != null and type.trim() != \"\"'>",
            "  AND template_type = #{type}",
            "</if>",
            "<if test='paramKey != null and paramKey.trim() != \"\"'>",
            "  AND param_key = #{paramKey}",
            "</if>",
            "AND param_value IS NOT NULL",
            "AND JSON_VALID(param_value) = 1",
            "AND param_value != '[]'",
            "ORDER BY knowledge_base_key, flow_no",
            " LIMIT #{offset}, #{limit} " +
                    "</script>"})
    List<TbKnowledgeBaseDataDTO> getAllValidJsonRecordsByType(
            @Param("type") String type,
            @Param("paramKey") String paramKey,
            @Param("offset") int offset,
            @Param("limit") int limit
    );
}
